﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.Linq;
using System.Linq;
using MyCalManager.Helper;
using MyCalManager.Model;
using System.IO;

namespace MyCalManager.Data
{
    public class CoreData
    {
        private static NLog.Logger logger = NLog.LogManager.GetCurrentClassLogger();
        private MCMDB DB = null;
        private WWPointsClass ww = new WWPointsClass();

        static readonly CoreData _instance = new CoreData();
        private Table<Foodstuff> Items = null;
        //private Table<DAYS> _Days = null;

        public int UserID { get; set; }
        public int CurrentDay { get; set; }
        public string LastError { get; set; }

        /// <summary>
        /// Returns the Core Data Instance
        /// </summary>
        public static CoreData Instance
        {
            get { return _instance; }
        }

        private CoreData()
        {
            Initialize();
        }

        private void Initialize()
        {
            
            string DBFile = AppDomain.CurrentDomain.BaseDirectory + "\\Data\\MCMDB.sdf";
            if (new FileInfo(DBFile).Exists)
            {
                DB = new MCMDB(DBFile);
                this.Items = this.DB.GetTable<Foodstuff>();
            }
            else
            {
                this.LastError = "NODBFILE";
            }
        }

        private void submitToDB()
        {
            var changeSet = this.DB.GetChangeSet();
            try
            {
                this.DB.SubmitChanges(ConflictMode.ContinueOnConflict);
            }
            catch (ChangeConflictException ex)
            {
                logger.Debug(ex.Message);
                foreach (ObjectChangeConflict occ in this.DB.ChangeConflicts)
                {
                    // All database values overwrite current values.
                    occ.Resolve(RefreshMode.KeepCurrentValues);
                }
                this.DB.SubmitChanges();
            }
        }


        #region FoodTable : Get, Add, Update and Delete

        private void ReloadFood()
        {
            this.Items = this.DB.GetTable<Foodstuff>();
        }

        /// <summary>
        /// Get Food table, Filter can be null, in this case all Food are returned
        /// </summary>
        /// <param name="Filter"></param>
        /// <returns></returns>
        public ObservableCollection<Foodstuff> Foods
        {
            get
            {
                return this.Items.OrderBy(s => s.Item).Select(s => s).ToObservableCollection();
            }
            //return this.Items.Where(s => s.Item.Contains(Filter)).OrderBy(s => s.Item).Select(s => s).ToObservableCollection();
        }

        public ObservableCollection<string> Categories
        {
            get
            {
                return this.Items.Select(s => s.Category).Distinct().OrderBy(s => s).ToObservableCollection();
            }
        }

        

        public bool AddFood(Foodstuff food)
        {
            food.WWPoints = ww.Calculate((int)food.Kcal, (double)food.Fat);
            food.ID = this.DB.Foodstuff.Select(s => s.ID).Max() + 1;
            try
            {
                this.DB.Foodstuff.InsertOnSubmit(food);
                this.submitToDB();
                this.ReloadFood();
                return true;
            }
            catch (SystemException ex)
            {
                logger.Debug(ex.Message);
                LastError = ex.Message;
                return false;
            }

        }

        public bool AddFood(string Item, int Kcal, float Fat, float Kh, float Carbs, string Categorie)
        {
            Foodstuff food = new Foodstuff();
            food.Item = Item;
            food.Kcal = Kcal;
            food.Fat = Fat;
            food.Kh = Kh;
            food.Carbs = Carbs;
            food.Category = Categorie;
            return this.AddFood(food);
        }

        public bool UpdateFood(Foodstuff food)
        {
           
            try
            {
                this.submitToDB();
                this.ReloadFood();
                return true;
            }
            catch (SystemException ex)
            {
                logger.Debug(ex.Message);
                LastError = ex.Message;
                return false;
            }
        }

        public bool DeleteFood(Foodstuff food)
        {

            try
            {
                this.DB.Foodstuff.DeleteOnSubmit(food);
                this.submitToDB();
                this.ReloadFood();
                return true;
            }
            catch (SystemException ex)
            {
                logger.Debug(ex.Message);
                LastError = ex.Message;
                return false;
            }
        }

        #endregion

        #region User : Get, Add and Delete

        public ObservableCollection<User> UserList
        {
            get
            {
                return this.DB.User.OrderBy(s => s.Name).ToObservableCollection();
            }
        }

        public bool AddUser(User user)
        {
            try
            {
                this.DB.User.InsertOnSubmit(user);
                this.submitToDB();
                return true;
            }
            catch (SystemException ex)
            {
                logger.Debug(ex.Message);
                return false;
            }
        }

        public bool DeleteUser(User user)
        {
            try
            {
                this.DB.User.DeleteOnSubmit(user);
                this.submitToDB();
                return true;
            }
            catch (SystemException ex)
            {
                logger.Debug(ex.Message);
                return false;
            }
        }

        public string GetCurrentUserName()
        {
            if (this.UserID != -1)
                return this.DB.User.Where(s => s.ID == this.UserID).Select(s => s.Vorname).Max();
            else
                return string.Empty;
        }

        #endregion

        #region Daylist : Get, Add, Update and Delete

        public ObservableCollection<Day> DayList
        {
            get
            {
                if (this.UserID == -1)
                    return null;
                
                return this.DB.DAYS.Where(s => s.UserID == this.UserID).OrderBy(s => s.Timekey).Select(s => new Day { ID = s.ID, 
                                                                                                                  Timekey = s.Timekey,
                                                                                                                  UserID = (int)s.UserID,
                                                                                                                  Gewicht = s.Gewicht,
                                                                                                                  SumKal = (decimal)s.Kal,
                                                                                                                  SumWWPoints = (decimal)s.WWPoints,
                                                                                                                  StringTimekey = s.Timekey.ToShortDateString()
                                                                                            }).ToObservableCollection();
            }
        }

        public bool AddDay(Day day)
        {
            DAYS d = day.ToDAYS();
            var ret = this.DB.DAYS.Where(s=> s.Timekey == d.Timekey && s.UserID == d.UserID).Select(s => s).Count();
            if (ret > 0)
            {
                return false;
            }
            try
            {
                var sel = this.DB.DAYS.Select(s => s.ID).ToList();
                if (sel.Count == 0)
                    d.ID = 0;
                else
                    d.ID = this.DB.DAYS.Select(s => s.ID).Max() + 1;
                this.DB.DAYS.InsertOnSubmit(d);
                this.submitToDB();
                return true;
            }
            catch (SystemException ex)
            {
                logger.Debug(ex.Message);
                LastError = ex.Message;
                return false;
            }
        }

        public bool UpdateDay(Day day)
        {
            DAYS d = this.DB.DAYS.Where(s => s.ID == day.ID).SingleOrDefault();

            
            if (d == null)
            {
                return false;
            }
            try
            {
                day.UpdateChanges(d);
                this.submitToDB();
                return true;
            }
            catch (SystemException ex)
            {
                logger.Debug(ex.Message);
                LastError = ex.Message;
                return false;
            }
        }

        public bool UpdateDay(DateTime time, decimal newWeight)
        {
            Day d = DayList.Where(s => s.Timekey == time).SingleOrDefault();

            d.Gewicht = newWeight;
            return UpdateDay(d);
        }

        public bool UpdateDay()
        {
            DAYS d = this.DB.DAYS.Where(s => s.ID == CurrentDay).SingleOrDefault();

            if (d == null)
            {
                return false;
            }

            d.Kal = GetSumKalOfDay();
            d.WWPoints = GetSumWWOfDay();
            try
            {
                this.submitToDB();
                return true;
            }
            catch (SystemException ex)
            {
                logger.Debug(ex.Message);
                LastError = ex.Message;
                return false;
            }
        }

        public decimal GetSumKalOfDay()
        {
            return this.DB.Breakfast.Where(s => s.DAYID == CurrentDay).Select(s => s.Kal).Sum().GetValueOrDefault(0) +
                   this.DB.Lunch.Where(s => s.DAYID == CurrentDay).Select(s => s.Kal).Sum().GetValueOrDefault(0) +
                   this.DB.Dinner.Where(s => s.DAYID == CurrentDay).Select(s => s.Kal).Sum().GetValueOrDefault(0) +
                   this.DB.Else.Where(s => s.DAYID == CurrentDay).Select(s => s.Kal).Sum().GetValueOrDefault(0);
        }

        public decimal GetSumWWOfDay()
        {
            return this.DB.Breakfast.Where(s => s.DAYID == CurrentDay).Select(s => s.WWPoints).Sum().GetValueOrDefault(0) +
                   this.DB.Lunch.Where(s => s.DAYID == CurrentDay).Select(s => s.WWPoints).Sum().GetValueOrDefault(0) +
                   this.DB.Dinner.Where(s => s.DAYID == CurrentDay).Select(s => s.WWPoints).Sum().GetValueOrDefault(0) +
                   this.DB.Else.Where(s => s.DAYID == CurrentDay).Select(s => s.WWPoints).Sum().GetValueOrDefault(0);
        }

        public bool DeleteDay(Day day)
        {
            DAYS d = this.DB.DAYS.Where(s => s.ID == day.ID).SingleOrDefault();
            
            if (d == null)
            {
                return false;
            }
            try
            {
                this.DB.DAYS.DeleteOnSubmit(d);
                this.submitToDB();
                return true;
            }
            catch (SystemException ex)
            {
                logger.Debug(ex.Message);
                LastError = ex.Message;
                return false;
            }
        }



        #endregion

        #region User Food List : Get, Add, Update and Delete

        public ObservableCollection<FoodItem> GetFoodCollection(FoodType Ftype)
        {
            
            switch (Ftype)
            {
                case FoodType.Breakfast:
                    return this.DB.Breakfast.Where(s => s.DAYID == CurrentDay).Select(s => new FoodItem 
                            {   ID = s.ID, 
                                Item = s.Item, 
                                Menge = s.Menge, 
                                Eiweiss = s.Eiweiss, 
                                Fett = s.Fett, 
                                Kal = s.Kal, 
                                Kh = s.Kh, 
                                WWPoints = s.WWPoints }).ToObservableCollection();
                case FoodType.Lunch:
                    return this.DB.Lunch.Where(s => s.DAYID == CurrentDay).Select(s => new FoodItem
                            {
                                ID = s.ID,
                                Item = s.Item,
                                Menge = s.Menge,
                                Eiweiss = s.Eiweiss,
                                Fett = s.Fett,
                                Kal = s.Kal,
                                Kh = s.Kh,
                                WWPoints = s.WWPoints
                            }).ToObservableCollection();
                case FoodType.Dinner:
                    return this.DB.Dinner.Where(s => s.DAYID == CurrentDay).Select(s => new FoodItem
                            {
                                ID = s.ID,
                                Item = s.Item,
                                Menge = s.Menge,
                                Eiweiss = s.Eiweiss,
                                Fett = s.Fett,
                                Kal = s.Kal,
                                Kh = s.Kh,
                                WWPoints = s.WWPoints
                            }).ToObservableCollection();
                case FoodType.Else:
                    return this.DB.Else.Where(s => s.DAYID == CurrentDay).Select(s => new FoodItem
                            {
                                ID = s.ID,
                                Item = s.Item,
                                Menge = s.Menge,
                                Eiweiss = s.Eiweiss,
                                Fett = s.Fett,
                                Kal = s.Kal,
                                Kh = s.Kh,
                                WWPoints = s.WWPoints
                            }).ToObservableCollection();
                default:
                    return null;
            }
        }

        

        public bool AddItem(FoodType Ftype, FoodItem Item)
        {
            try
            {
                switch (Ftype)
                {
                    case FoodType.Breakfast:
                        var i = Item.ToBreakfastItem();
                        i.DAYID = CurrentDay;
                        this.DB.Breakfast.InsertOnSubmit(i);
                        break;
                    case FoodType.Lunch:
                        var j = Item.ToLunchItem();
                        j.DAYID = CurrentDay;
                        this.DB.Lunch.InsertOnSubmit(j);
                        break;
                    case FoodType.Dinner:
                        var k = Item.ToDinnerItem();
                        k.DAYID = CurrentDay;
                        this.DB.Dinner.InsertOnSubmit(k);
                        break;
                    case FoodType.Else:
                        var l = Item.ToElseItem();
                        l.DAYID = CurrentDay;
                        this.DB.Else.InsertOnSubmit(l);
                        break;
                }

                this.submitToDB();
                return true;
            }
            catch (SystemException ex)
            {
                logger.Debug(ex.Message);
                LastError = ex.Message;
                return false;
            }
        }

        public bool UpdateItem(FoodType Ftype, FoodItem Item)
        {
            try
            {
                switch (Ftype)
                {
                    case FoodType.Breakfast:
                        var i = this.DB.Breakfast.Single(s => s.ID == Item.ID);
                        i.Kal = Item.Kal;
                        i.Menge = Item.Menge;
                        i.Kh = Item.Kh;
                        i.Fett = Item.Fett;
                        i.Eiweiss = Item.Eiweiss;
                        i.WWPoints = Item.WWPoints;
                        break;
                    case FoodType.Lunch:
                        var j = this.DB.Lunch.Single(s => s.ID == Item.ID);
                        j.Kal = Item.Kal;
                        j.Menge = Item.Menge;
                        j.Kh = Item.Kh;
                        j.Fett = Item.Fett;
                        j.Eiweiss = Item.Eiweiss;
                        j.WWPoints = Item.WWPoints;
                        break;
                    case FoodType.Dinner:
                        var k = this.DB.Dinner.Single(s => s.ID == Item.ID);
                        k.Kal = Item.Kal;
                        k.Menge = Item.Menge;
                        k.Kh = Item.Kh;
                        k.Fett = Item.Fett;
                        k.Eiweiss = Item.Eiweiss;
                        k.WWPoints = Item.WWPoints;
                        break;
                    case FoodType.Else:
                        var l = this.DB.Else.Single(s => s.ID == Item.ID);
                        l.Kal = Item.Kal;
                        l.Menge = Item.Menge;
                        l.Kh = Item.Kh;
                        l.Fett = Item.Fett;
                        l.Eiweiss = Item.Eiweiss;
                        l.WWPoints = Item.WWPoints;
                        break;
                }

                this.submitToDB();
                return true;
            }
            catch (SystemException ex)
            {
                logger.Debug(ex.Message);
                LastError = ex.Message;
                return false;
            }
        }

        public bool DeleteItem(FoodType Ftype, FoodItem Item)
        {
            try
            {
                switch (Ftype)
                {
                    case FoodType.Breakfast:
                        var i = this.DB.Breakfast.Single(s => s.ID == Item.ID);
                        this.DB.Breakfast.DeleteOnSubmit(i);
                        break;
                    case FoodType.Lunch:
                        var j = this.DB.Lunch.Single(s => s.ID == Item.ID);
                        this.DB.Lunch.DeleteOnSubmit(j);
                        break;
                    case FoodType.Dinner:
                        var k = this.DB.Dinner.Single(s => s.ID == Item.ID);
                        this.DB.Dinner.DeleteOnSubmit(k);
                        break;
                    case FoodType.Else:
                        var l = this.DB.Else.Single(s => s.ID == Item.ID);
                        this.DB.Else.DeleteOnSubmit(l);
                        break;
                }

                this.submitToDB();
                return true;
            }
            catch (SystemException ex)
            {
                logger.Debug(ex.Message);
                LastError = ex.Message;
                return false;
            }

            
        }

        #endregion

        #region Statistik


        public ObservableCollection<MonthItem> GetMonths()
        {
            
            var res = this._GetMonths().GroupBy(s => s.Timekey.ToString("MMM yyyy"))
                .Select(sg => new MonthItem
                {
                    Month = sg.Key,
                    First = (decimal)sg.Select(s => s.Weight).First(),
                    Last =  (decimal)sg.Select(s => s.Weight).Last(),
                    Diff = (decimal)sg.Select(s => s.Weight).Last() - (decimal)sg.Select(s => s.Weight).First()
                }).ToObservableCollection();
            return res;
        }

        public ObservableCollection<MonthItem> GetYears()
        {

            var res = this._GetMonths().GroupBy(s => s.Timekey.ToString("yyyy"))
                .Select(sg => new MonthItem
                {
                    Month = sg.Key,
                    First = (decimal)sg.Select(s => s.Weight).First(),
                    Last = (decimal)sg.Select(s => s.Weight).Last(),
                    Diff = (decimal)sg.Select(s => s.Weight).Last() - (decimal)sg.Select(s => s.Weight).First()
                }).ToObservableCollection();
            return res;
        }

        private ObservableCollection<MonthItem> _GetMonths()
        {
            return this.DB.DAYS.Where(s => s.UserID == this.UserID).OrderBy(s => s.Timekey)
               .Select(s => new MonthItem
               {
                   Timekey = s.Timekey,
                   Weight = (decimal)s.Gewicht
               }).ToObservableCollection();
        }

        public decimal GetDiff()
        {
            DateTime min = this.DB.DAYS.Min(s => s.Timekey);
            DateTime max = this.DB.DAYS.Max(s => s.Timekey);
            decimal MinG = this.DB.DAYS.Where(s => s.Timekey == min).Select(s => s.Gewicht).Single();
            decimal MaxG = this.DB.DAYS.Where(s => s.Timekey == max).Select(s => s.Gewicht).Single();
            return MaxG - MinG;
        }

        #endregion

    }
}
